'''
数据库相关的操作
'''
import pymysql #
def connect(db_info):
    # 连接数据库，db_info是个字典，包含数据库相关的信息
    # db = {"ip": "192.168.124.85", "port": 3306, "user": "root", "pwd": "123456", "name": "apple"}
    # 面试可能会问：连接数据库用的哪个包，用到哪个方法，以及哪些参数？ 联系sqlyog记忆
    conn = pymysql.connect(user=db_info['user'],
        password=db_info['pwd'],
        host=db_info['ip'],
        database=db_info['name'],
        port=db_info['port'],
        charset="utf8")
    print("连接数据库成功")
    return conn

def disconnect(conn):
    # 断开连接
    conn.close()
    print("断开数据库连接成功")

def execute(conn,sql):
    # 执行sql语句
    c = conn.cursor() # 获取游标
    c.execute(sql) # 执行sql语句
    conn.commit() # 提交
    print(f"执行sql语句{sql}成功")
    return c.fetchall()  # 获取结果

def get_amount(db, phone):
    a = connect(db)
    b = execute(a, f"select LeaveAmount from member where mobilephone={phone}")
    amount = 0
    if len(b) != 0: # 有值时： ((Decimal('50000.00'),),)  没值时 ()
        amount = b[0][0]
        # print(amount)
    disconnect(a)
    return amount

def delete_user(db, phone):
    # 根据手机号码删除用户
    a = connect(db)
    execute(a, f"delete from member where mobilephone={phone}")
    disconnect(a)

# 测试代码，用完可以删除
if __name__ == '__main__':
    db = {"ip": "192.168.124.85", "port": 3306, "user": "root", "pwd": "123456", "name": "apple"}
    # a = connect(db)
    # execute(a, "delete from member where mobilephone=18164319098")  # 根据手机号码删除用户
    # disconnect(a)
    delete_user(db, "18164319098")
    get_amount(db, "18012341234")  #
    get_amount(db, "13245")  # ()